using OfficeOpenXml;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using Microsoft.AspNetCore.Hosting;
using System.IO;
using System.Collections.Generic;
using System.Globalization;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace Tools.Controllers
{
    [ApiController]
    [Route("api/[controller]")]
    public class ImportPh3Controller : ControllerBase
    {
        private readonly IWebHostEnvironment _environment;
        private readonly ILogger<ImportPh3Controller> _logger;
        


        public ImportPh3Controller(IWebHostEnvironment environment, ILogger<ImportPh3Controller> logger)
        {
            _environment = environment;
            _logger = logger;
            // 设置EPPlus许可证上下文
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        }

        [HttpPost("/api/import/ph3-data")]
        public async Task<IActionResult> ImportPh3Data([FromForm] IFormFile file)
        {
            try
            {
                if (file == null || file.Length == 0)
                {
                    return BadRequest(new { success = false, message = "请选择文件" });
                }

                // 验证文件类型（扩展名和内容）
                var validationResult = ValidateFile(file);
                if (!validationResult.Success)
                {
                    return BadRequest(new { success = false, message = validationResult.Message });
                }

                // 读取模板文件
                var templatePath = Path.Combine(_environment.ContentRootPath, "Templates", "PH3数据生成.xlsx");
                if (!System.IO.File.Exists(templatePath))
                {
                    return StatusCode(500, new { success = false, message = "未找到PH3数据生成.xlsx模板文件" });
                }

                // 验证模板文件
                var templateValidation = await ValidateExcelFile(templatePath);
                if (!templateValidation.Success)
                {
                    return StatusCode(500, new { success = false, message = $"模板文件验证失败: {templateValidation.Message}" });
                }

                // 创建内存流保存结果
                using (var memoryStream = new MemoryStream())
                {
                    // 复制模板到内存流
                    using (var templateStream = new FileStream(templatePath, FileMode.Open, FileAccess.Read))
                    {
                        await templateStream.CopyToAsync(memoryStream);
                    }

                    // 重置内存流位置
                    memoryStream.Position = 0;

                    // 使用EPPlus处理Excel
                    using (var package = new ExcelPackage(memoryStream))
                    {
                        // 读取上传的文件
                        using (var fileStream = file.OpenReadStream())
                        {
                            // 验证上传文件内容
                            if (!IsValidExcelFile(fileStream))
                            {
                                return BadRequest(new { success = false, message = "上传的文件不是有效的Excel文件或已损坏" });
                            }

                            // 重置流位置
                            fileStream.Position = 0;

                            using (var uploadedPackage = new ExcelPackage(fileStream))
                            {
                                // 处理数据并捕获错误
                                var (success, errorMessage) = ProcessPh3Data(uploadedPackage, package);
                                if (!success)
                                {
                                    return StatusCode(500, new { success = false, message = errorMessage });
                                }
                            }
                        }

                        // 保存处理后的文件
                        await package.SaveAsync();
                        memoryStream.Position = 0;

                        // 返回文件
                        var fileName = $"PH3处理结果_{DateTime.Now:yyyyMMddHHmmss}.xlsx";
                        return File(memoryStream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);
                    }
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "处理PH3数据时发生异常");
                return StatusCode(500, new { success = false, message = $"处理数据时发生错误: {ex.Message}" });
            }
        }

        private (bool Success, string Message) ProcessPh3Data(ExcelPackage uploadedPackage, ExcelPackage templatePackage)
        {
            try
            {
                // 获取上传文件的第一个工作表
                var uploadedSheet = uploadedPackage.Workbook.Worksheets[0];
                if (uploadedSheet.Dimension == null)
                {
                    return (false, "上传文件没有数据");
                }

                // 获取模板文件的第一个工作表
                var templateSheet = templatePackage.Workbook.Worksheets[0];
                if (templateSheet.Dimension == null)
                {
                    return (false, "模板文件没有数据");
                }

                // 清空模板文件中除表头外的所有数据行
                if (templateSheet.Dimension.Rows > 1)
                {
                    templateSheet.DeleteRow(2, templateSheet.Dimension.Rows - 1);
                }

                // 定义字段映射关系
                var fieldMapping = new Dictionary<string, string>
                {
                    { "大单位", "大单位" },
                    { "厂家", "厂家" },
                    { "规格", "规格" },
                    { "药品编码", "药品编码" },
                    { "药品名称", "药品名称" },
                    { "成本", "成本" },
                    { "国药准字", "国药准字" },
                    { "医保码", "医保码" },
                    { "拆零数量", "拆零数量" },
                    { "总部售价", "总部售价" },
                    { "门店售价", "门店售价" },
                    { "原始类型", "原始类型" },
                    { "类型", "类型" },
                    { "单位", "单位" },
                    { "当前库存", "当前库存" },
                    { "可售库存", "可售库存" },
                    { "拆零单位", "拆零单位" }
                };

                // 获取上传文件的表头行
                var uploadedHeaders = new Dictionary<string, int>();
                int headerRow = 1;
                for (int col = 1; col <= uploadedSheet.Dimension.End.Column; col++)
                {
                    string header = uploadedSheet.Cells[headerRow, col].Text.Trim();
                    if (!string.IsNullOrEmpty(header))
                    {
                        uploadedHeaders[header] = col;
                    }
                }



                // 获取模板文件的表头行
                var templateHeaders = new Dictionary<string, int>();
                for (int col = 1; col <= templateSheet.Dimension.End.Column; col++)
                {
                    string header = templateSheet.Cells[headerRow, col].Text.Trim();
                    if (!string.IsNullOrEmpty(header))
                    {
                        templateHeaders[header] = col;
                    }
                }

                // 在模板文件最右侧添加"检查结果"列
                int checkResultCol = templateSheet.Dimension.End.Column + 1;
                templateSheet.Cells[headerRow, checkResultCol].Value = "检查结果";
                templateSheet.Cells[headerRow, checkResultCol].Style.Font.Bold = true;

                // 处理数据行
                int startRow = 2; // 从第二行开始处理数据
                int maxRows = Math.Min(uploadedSheet.Dimension.End.Row, 10000); // 限制最多处理10000行数据

                for (int row = startRow; row <= maxRows; row++)
                {
                    // 插入新行
                    if (row > startRow)
                    {
                        templateSheet.InsertRow(2, 1);
                    }

                    // 检查结果
                    List<string> checkResults = new List<string>();

                    // 遍历所有字段映射
                    foreach (var mapping in fieldMapping)
                    {
                        string sourceField = mapping.Key;
                        string targetField = mapping.Value;

                        // 检查源字段和目标字段是否存在
                        if (uploadedHeaders.ContainsKey(sourceField) && templateHeaders.ContainsKey(targetField))
                        {
                            int sourceCol = uploadedHeaders[sourceField];
                            int targetCol = templateHeaders[targetField];

                            // 获取源数据
                            var cell = uploadedSheet.Cells[row, sourceCol];
                            object sourceValue = cell.Value;
                            string sourceText = cell.Text.Trim();
                            string processedValue = sourceText;

                            // 应用数据处理规则
                            switch (sourceField)
                            {
                                case "大单位":
                                    // 规则1：检查大单位是否有空格
                                    if (sourceText.Contains(" "))
                                    {
                                        checkResults.Add($"大单位 '{sourceText}' 包含空格");
                                    }
                                    break;

                                case "厂家":
                                    // 规则2：厂家不能为空，为空填写'无'
                                    if (string.IsNullOrEmpty(sourceText))
                                    {
                                        processedValue = "无";
                                    }
                                    break;

                                case "规格":
                                    // 规则3：规格中 X 更改为*，规格中 / 更改为*
                                    processedValue = sourceText.Replace('X', '*').Replace('/', '*');
                                    
                                    // 规则6：规格中不能有特殊字符显示,如'E+'
                                    if (sourceText.Contains("E+"))
                                    {
                                        checkResults.Add($"规格 '{sourceText}' 包含特殊字符 'E+'");
                                    }
                                    break;

                                case "药品编码":
                                    // 规则4：药品编码位数不能等于14
                                    if (sourceText.Length == 14 && sourceText.All(char.IsDigit))
                                    {
                                        checkResults.Add($"药品编码 '{sourceText}' 位数等于14");
                                    }
                                    
                                    // 规则16：长数字设置为纯文本
                                    if (IsLongNumber(sourceText))
                                    {
                                        templateSheet.Cells[2, targetCol].Style.Numberformat.Format = "@";
                                    }
                                    break;

                                case "药品名称":
                                    // 规则5：药品名称前不能包含特殊字符，如'*'
                                    if (!string.IsNullOrEmpty(sourceText) && sourceText.StartsWith("*"))
                                    {
                                        checkResults.Add($"药品名称 '{sourceText}' 以特殊字符开头");
                                    }
                                    break;

                                case "成本":
                                    // 规则7：成本是否为空（为空填0）
                                    if (string.IsNullOrEmpty(sourceText))
                                    {
                                        processedValue = "0";
                                    }
                                    break;

                                case "国药准字":
                                    // 规则8：国药准字表头处理
                                    if (templateHeaders.ContainsKey("类型"))
                                    {
                                        int typeCol = templateHeaders["类型"];
                                        if (sourceText.Contains("国药准字"))
                                        {
                                            templateSheet.Cells[2, typeCol].Value = "西药中成药";
                                        }
                                        else if (sourceText.Contains("械"))
                                        {
                                            templateSheet.Cells[2, typeCol].Value = "耗材";
                                        }
                                        else if (sourceText.Contains("消"))
                                        {
                                            templateSheet.Cells[2, typeCol].Value = "耗材";
                                        }
                                    }
                                    break;

                                case "医保码":
                                    // 规则9：医保码处理
                                    if (templateHeaders.ContainsKey("类型"))
                                    {
                                        int typeCol = templateHeaders["类型"];
                                        if (!string.IsNullOrEmpty(sourceText))
                                        {
                                            if (sourceText.StartsWith("T"))
                                            {
                                                templateSheet.Cells[2, typeCol].Value = "中药";
                                            }
                                            else if (sourceText.StartsWith("C"))
                                            {
                                                templateSheet.Cells[2, typeCol].Value = "耗材";
                                            }
                                            else
                                            {
                                                templateSheet.Cells[2, typeCol].Value = "西药中成药";
                                            }
                                        }
                                    }
                                    
                                    // 规则16：长数字设置为纯文本
                                    if (IsLongNumber(sourceText))
                                    {
                                        templateSheet.Cells[2, targetCol].Style.Numberformat.Format = "@";
                                    }
                                    break;

                                case "拆零数量":
                                    // 规则10：拆零数量处理
                                    if (int.TryParse(sourceText, out int splitQty))
                                    {
                                        if (templateHeaders.ContainsKey("单位") && 
                                            templateHeaders.ContainsKey("当前库存") && 
                                            templateHeaders.ContainsKey("可售库存") && 
                                            templateHeaders.ContainsKey("拆零单位"))
                                        {
                                            int unitCol = templateHeaders["单位"];
                                            int currentStockCol = templateHeaders["当前库存"];
                                            int sellableStockCol = templateHeaders["可售库存"];
                                            int splitUnitCol = templateHeaders["拆零单位"];

                                            string unitValue = uploadedHeaders.ContainsKey("单位") ? uploadedSheet.Cells[row, uploadedHeaders["单位"]].Text.Trim() : "";
                                            string currentStockValue = uploadedHeaders.ContainsKey("当前库存") ? uploadedSheet.Cells[row, uploadedHeaders["当前库存"]].Text.Trim() : "";
                                            string sellableStockValue = uploadedHeaders.ContainsKey("可售库存") ? uploadedSheet.Cells[row, uploadedHeaders["可售库存"]].Text.Trim() : "";
                                            string splitUnitValue = uploadedHeaders.ContainsKey("拆零单位") ? uploadedSheet.Cells[row, uploadedHeaders["拆零单位"]].Text.Trim() : "";

                                            // 提取中文单位
                                            string unitChinese = ExtractChineseUnit(unitValue);
                                            string currentStockChinese = ExtractChineseUnit(currentStockValue);
                                            string sellableStockChinese = ExtractChineseUnit(sellableStockValue);
                                            string splitUnitChinese = ExtractChineseUnit(splitUnitValue);

                                            if (splitQty == 1)
                                            {
                                                // 拆零数量=1时，所有单位应一致
                                                if (!string.IsNullOrEmpty(unitChinese))
                                                {
                                                    // 处理当前库存
                                                    if (!string.IsNullOrEmpty(currentStockValue) && !currentStockChinese.Equals(unitChinese))
                                                    {
                                                        string numericPart = ExtractNumericPart(currentStockValue);
                                                        templateSheet.Cells[2, currentStockCol].Value = $"{numericPart}{unitChinese}";
                                                    }

                                                    // 处理可售库存
                                                    if (!string.IsNullOrEmpty(sellableStockValue) && !sellableStockChinese.Equals(unitChinese))
                                                    {
                                                        string numericPart = ExtractNumericPart(sellableStockValue);
                                                        templateSheet.Cells[2, sellableStockCol].Value = $"{numericPart}{unitChinese}";
                                                    }

                                                    // 处理拆零单位
                                                    if (!string.IsNullOrEmpty(splitUnitChinese) && !splitUnitChinese.Equals(unitChinese))
                                                    {
                                                        templateSheet.Cells[2, splitUnitCol].Value = unitChinese;
                                                    }
                                                }
                                            }
                                            else if (splitQty > 1)
                                            {
                                                // 拆零数量>1时，单位和拆零单位应不同
                                                if (!string.IsNullOrEmpty(unitChinese) && !string.IsNullOrEmpty(splitUnitChinese) && unitChinese.Equals(splitUnitChinese))
                                                {
                                                    checkResults.Add($"拆零数量>1时，单位和拆零单位不能相同");
                                                }
                                            }

                                            // 处理当前库存和可售库存的单位一致性
                                            if (!string.IsNullOrEmpty(unitChinese))
                                            {
                                                // 当前库存单位与单位列一致
                                                if (!string.IsNullOrEmpty(currentStockValue) && !currentStockChinese.Equals(unitChinese) && !currentStockChinese.Equals(splitUnitChinese))
                                                {
                                                    string numericPart = ExtractNumericPart(currentStockValue);
                                                    templateSheet.Cells[2, currentStockCol].Value = $"{numericPart}{unitChinese}";
                                                }

                                                // 可售库存单位与单位列一致
                                                if (!string.IsNullOrEmpty(sellableStockValue) && !sellableStockChinese.Equals(unitChinese) && !sellableStockChinese.Equals(splitUnitChinese))
                                                {
                                                    string numericPart = ExtractNumericPart(sellableStockValue);
                                                    templateSheet.Cells[2, sellableStockCol].Value = $"{numericPart}{unitChinese}";
                                                }
                                            }
                                        }
                                    }
                                    break;

                                case "总部售价":
                                case "门店售价":
                                    // 规则11：总部售价、门店售价字段为0或者空改为1
                                    if (string.IsNullOrEmpty(sourceText) || sourceText == "0")
                                    {
                                        processedValue = "1";
                                    }
                                    break;

                                case "原始类型":
                                    // 规则12：原始类型表头处理
                                    if (templateHeaders.ContainsKey("类型"))
                                    {
                                        int typeCol = templateHeaders["类型"];
                                        if (sourceText.Contains("中药"))
                                        {
                                            templateSheet.Cells[2, typeCol].Value = "中药";
                                        }
                                        else if (sourceText.Contains("普通及功能食品") || sourceText.Contains("国食保健食品") || sourceText.Contains("化妆品类"))
                                        {
                                            templateSheet.Cells[2, typeCol].Value = "其他商品";
                                        }
                                        else if (sourceText.Contains("医疗器械") || sourceText.Contains("消毒及卫生用品"))
                                        {
                                            templateSheet.Cells[2, typeCol].Value = "耗材";
                                        }
                                    }
                                    break;

                                default:
                                    // 规则16：长数字设置为纯文本
                                    if (IsLongNumber(sourceText))
                                    {
                                        templateSheet.Cells[2, targetCol].Style.Numberformat.Format = "@";
                                    }
                                    break;
                            }

                            // 设置目标单元格的值
                            var targetCell = templateSheet.Cells[2, targetCol];
                            if (int.TryParse(processedValue, out int intValue))
                            {
                                targetCell.Value = intValue;
                            }
                            else if (decimal.TryParse(processedValue, out decimal decimalValue))
                            {
                                targetCell.Value = decimalValue;
                            }
                            else
                            {
                                targetCell.Value = processedValue;
                            }
                        }
                    }

                    // 设置检查结果
                    templateSheet.Cells[2, checkResultCol].Value = string.Join("; ", checkResults);
                }

                return (true, string.Empty);
            }
            catch (Exception ex)
            {
                return (false, $"处理数据时发生错误: {ex.Message}");
            }
        }

        // 验证文件类型和大小
        private (bool Success, string Message) ValidateFile(IFormFile file)
        {
            // 检查文件大小（限制10MB）
            if (file.Length > 10 * 1024 * 1024)
            {
                return (false, "文件大小不能超过10MB");
            }

            // 检查文件扩展名
            var ext = Path.GetExtension(file.FileName).ToLowerInvariant();
            if (ext != ".xlsx" && ext != ".xls")
            {
                return (false, "只支持Excel文件(.xlsx, .xls)");
            }

            return (true, string.Empty);
        }

        // 验证Excel文件是否有效
        private async Task<(bool Success, string Message)> ValidateExcelFile(string filePath)
        {
            try
            {
                using (var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    if (!IsValidExcelFile(stream))
                    {
                        return (false, "文件不是有效的Excel文件或已损坏");
                    }

                    stream.Position = 0;
                    using (var package = new ExcelPackage(stream))
                    {
                        // 尝试访问工作表以验证内容
                        if (package.Workbook.Worksheets.Count == 0)
                        {
                            return (false, "Excel文件中没有工作表");
                        }
                    }
                }

                return (true, string.Empty);
            }
            catch (Exception ex)
            {
                return (false, ex.Message);
            }
        }

        // 检查文件是否为有效的Excel文件（基于文件头）
        private bool IsValidExcelFile(Stream stream)
        {
            // 保存当前位置
            long originalPosition = stream.Position;
            try
            {
                // 读取文件头字节
                byte[] headerBytes = new byte[8];
                int bytesRead = stream.Read(headerBytes, 0, headerBytes.Length);
                if (bytesRead < 8)
                {
                    return false; // 文件太小，不可能是有效的Excel文件
                }

                // .xlsx文件的魔术数字 (PK ZIP格式)
                bool isXlsx = headerBytes[0] == 0x50 && headerBytes[1] == 0x4B && 
                              (headerBytes[2] == 0x03 && headerBytes[3] == 0x04 || 
                               headerBytes[2] == 0x05 && headerBytes[3] == 0x06 || 
                               headerBytes[2] == 0x07 && headerBytes[3] == 0x08);

                // .xls文件的魔术数字 (BIFF8格式)
                bool isXls = headerBytes[0] == 0xD0 && headerBytes[1] == 0xCF && 
                             headerBytes[2] == 0x11 && headerBytes[3] == 0xE0 && 
                             headerBytes[4] == 0xA1 && headerBytes[5] == 0xB1 && 
                             headerBytes[6] == 0x1A && headerBytes[7] == 0xE1;

                return isXlsx || isXls;
            }
            finally
            {
                // 恢复流位置
                stream.Position = originalPosition;
            }
        }

        // 判断是否为需要设置为文本格式的长数字
        private bool IsLongNumber(string value)
        {
            if (string.IsNullOrEmpty(value)) return false;
            return value.All(char.IsDigit) && value.Length > 6;
        }

        // 提取中文单位部分
        private string ExtractChineseUnit(string value)
        {
            if (string.IsNullOrEmpty(value)) return string.Empty;
            // 匹配中文单位
            var match = Regex.Match(value, @"[一-龥]+$");
            return match.Success ? match.Value : string.Empty;
        }

        // 提取数字部分
        private string ExtractNumericPart(string value)
        {
            if (string.IsNullOrEmpty(value)) return string.Empty;
            // 匹配数字部分
            var match = Regex.Match(value, @"^[0-9.]+(?=[一-龥])");
            return match.Success ? match.Value : value;
        }
    }
}